Mysql REPEATABLE READ Problem

前言

經過 pg mvcc 與 mysql mvcc 的介紹後，
我們知道 SX lock 和 MVCC 都能逹到 Isolation 目的，
但 SX lock 為什麼只加上 ReadView ， write 呢？
我們來細細的分析可能會有什麼問題。

兩種 isolation 流派

兩種 isolation 流派
SX lock 和 MVCC 都能逹到 Isolation 目的，

• MVCC: PostgreSQL， Oracle
• SX lock: MySQL， MSSQL

因為其底層機制不同，所以雖然大家都說自己支持 ACID ，
支持 4 個 isolation level ，其 isolation 的行為卻有所不同

MySQL 和 MSSQL 近年試圖在其 SX lock 架構上再加上 MVCC。

回顧 REPEATABLE READ

• Read commit:
  只讀有 commit 過的，解決了 dirty read。
  所以不會發生 dirty read 但是會發生 Non-Repeatable，
  Non-Repeatable 是指同一個 tx 下，兩次 select 中間，如果有其他 tx 做 update，兩次的 select 結果會不同
• Repeatable:
  讀過的不能改，解決了 Non-Repeatable。
  所以不會發生 Non-Repeatable 但是會發生 幻讀(Phantom read)，
  Phantom read 是指同一個 tx 下，兩次 select 中間，如果有其他 tx 做 insert，兩次的 select 結果會不同

所以 RR(REPEATABLE READ)本來就不防幻讀，
如果有人跟你說 mysql RR 發生幻讀， 你要跟他說非常正常，
因為每個 db 的 RR 都會發生幻， 而且理應發生。

問題重現

聽起來似乎都沒有問題，一且都完美的呈現， 但問題在哪裡呢？請聽我娓娓道來。

remark: Mysql RR problem(5.7，8.0 可重現)

一般情況下

我們就直接來舉的例子比較清楚

假設原資料 num=0
tx2 update num=num+2

REPEATABLE READ 只有 time2 會建立 readview， time5 直接使用 time2 的 readview

tx2 time2: 預期看到 num=0

//原表
{"num":0,"trx_id": 0}

//ReadView
{ "creator_trx_id": "tx1", "m_ids": [ 1,2 ], "up_limit_id": 1, "low_limit_id": 2}
// 取 {"num":0,"trx_id": 0}

• 條件 1: creator_trx_id(tx2)=trx_id(0) false

• 條件 2:

  • 原表 trx_id=3， [trx_id<=up_limit_id]<___<low_limit_id， return true

• 結論: readview 可以利用目前表上的資料，直接讀原表該紀錄的尾巴，即可建立 readview， 記下這張 readview

tx2 time5: 預期看到 num=0

//原表
{"num":0,"trx_id": 0}<= {"num":0+2,"trx_id": 2}

// ReadView
// 直接抓上次的 {"name":0,"trx_id": 0}

• 結論: 直接抓上次的 readview

read-write conflict

可以清楚地看到， 在 REPEATABLE READ 的 level 下，我們可以正常的讀到同一個結果。

但是

如果把 time5 改成 Write 呢？
我們就直接來舉的例子比較清楚

假設原資料 num=0
tx1 update num=num+1
tx2 update num=num+2

我們來探討一下 tx1 的 time2,5,6 與 tx3 的 8 吧！

tx2 time2: 預期看到 num=0

time2 跟上面一樣，可成功抓到 num=0，這裡就不多贅述，可以往上翻

tx2 time5: 預期會因為 Write-write conflict Block 住，或者 num=0+1

我們來解析一下 tx1 time5 的預期效果
update num=num+1
在更新之前， 我們再加入一個 select 來看看

1. select num from ...
2. update ... set num=num+1

所以會先 select 取得 num 的值來確認一下是 update 之前的 num 是什麼

假設我們就先來個select num from ...

//原表
{"num":0,"trx_id": 0}<= {"num":0+2,"trx_id": 2}
// ReadView
// 直接抓上次的 {"name":0,"trx_id": 0}

所以可以看到了 num=0， 因為是抓取上次的 ReadView，看起來十分正常，讓我們繼續看下去

再來 update ... set num=${num}+1

上一步取得了 num=0，那麼我想+1， 應該會得到 num=1

//原表 {"num":0,"trx_id": 0} override by tx2
//      ↑→→→→→→→→→→→→→→→→→→→→→→→→→↓
{"num":0+2,"trx_id": 2}<= {"num":(0+2)+1,"trx_id": 1}

等等， 怎麼 num 變成了(0+2)+1 呢?說好的 0+1 呢？
怎麼會查出來的值跟預期中的不同？ 應該只是暫時不一樣吧？等等下一步 select 出來應該會是說好的

tx2 time6: 預期會因為 Write-write conflict 覆蓋掉 num=0+2 變成 num=0+1

//原表 {"num":0,"trx_id": 0} override by tx2
//      ↑→→→→→→→→→→→→→→→→→→→→→→→→→↓
{"num":0+2,"trx_id": 2}<= {"num":(0+2)+1,"trx_id": 1}
// ReadView
// 直接抓上次的{"num":(0+2)+1,"trx_id": 1}

• 條件 1: creator_trx_id(tx1)=trx_id(1) return true
• 結論: 在同一個tx底下改完讀取,符合條件1

出來結果是 num=(0+2)+1， 對，你沒看錯，變成3完全出乎意料之外， 驚不驚喜意不意外啊！

tx3 time8: 預期會因為 Write-write conflict 覆蓋掉 num=0+2 變成 num=0+1

//原表 {"num":0,"trx_id": 0} {"num":0+2,"trx_id": 2} override by tx1
//      ↓←←←←←←←←←←←←←←←←←←←←←←←←←←←↓
{"num":(0+2)+1,"trx_id": 1}
// ReadView
{ "creator_trx_id": "tx3", "m_ids": [3], "up_limit_id": 3, "low_limit_id": 3}
// 取 {"num":(0+2)+1,"trx_id": 1}

• 條件 1: creator_trx_id(tx2)=trx_id(0) false

• 條件 2:

  • 原表 trx_id=1， [trx_id<=up_limit_id]<___<low_limit_id， return true

• 結論: readview 可以利用目前表上的資料，直接讀原表該紀錄的尾巴，即可建立 readview， 記下這張 readview

所以出來結果是 num=3 (0+2+1)， 完全沒有出現預期中的 num=1

問題分析

RR 是防止同一列讀第二次數值不同。
理想上 read/write 會抓到同一個版本的值，而非最新 commit 點。
因為 mysql 骨子裡其實還是 sxlock，純 sxlock 遇到 read-write conflict 時，會發生 block
所以透過 readview 來實作 mvcc 的關係，事實上就是快照讀， 當前寫，
因此 mysql 只符合 read version 相同。

postgres 會如何處理這個問題呢？

postgres 因為是純樂觀鎖所建構出的 mvcc，
在 tx1 time5 會 throw error， 來拒絕 tx1 的 write

使用 mysql 的用戶如何解決 mysql RR Problem 呢？

問題在於 mysql 在 REPEATABLE READ Level 下， 原 tx 建立了 readview
其他 tx 修改後，原 tx 還能修改同一紀錄或原 tx 修改的版本與 readview 會呈現的版本不同
因此

1. 禁止其他 tx 在 select 後修改，重現 read-write conflict
   • 使用悲觀顯示鎖: 在 tx1 time 加上select ... for update，將該筆紀錄完全鎖上，避免中間被別人修改
2. 讓原 tx 修改只能修改到相同版本的 ReadView
   • DIY 樂觀鎖(Optimistic Lock): 修改整個表格，讓 tx1 的 time5 update 筆紀錄，使用下一個 tx 來做 retry

結語

任何本質上是 SX lock，但又支援 MVCC 的 RDBMS，
雖然解決的 read-write conflict，
但如果在 REPEATABLE READ level 下，
在做 write-write conflict 時請務必要特別注意喔!

參考資料

1. Triton Ho 的貼文